/*
 * Copyright 2006-2022 ahtelit.com, All Rights Reserved.
 */

package com.telit.firecommand.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.telit.firecommand.entity.SysUser;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;


/**
 * SysUserDAO
 *
 * @author yuezhuang
 * @since 2023-01-17
 */
@Repository
public interface SysUserDAO extends BaseMapper<SysUser> {

    @Select("<script> "
            + "select su.id, su.longitude, su.latitude, su.user_online_status, su.district_code, su.mobile "
            + "from sys_user su "
            + "inner join sys_user_role sur on su.id = sur.user_id "
            + "where su.is_deleted = 1 "

            + "<if test=\"account != null\"> "
            + "and concat_ws('', su.user_name, su.real_name) like concat('%', #{account}, '%') "
            + "</if> "

            + "<if test=\"regionCode != null and regionCode != '' and areaRecursion == true \"> "
            + "and su.district_code like concat(#{regionCode},'%') "
            + "</if> "

            + "<if test=\"regionCode != null and regionCode != '' and areaRecursion == false \"> "
            + "and su.district_code = #{regionCode} "
            + "</if> "

            + "<if test=\"userOnlineStatus != null\"> "
            + "AND su.user_online_status = #{userOnlineStatus} "
            + "</if> "

            + "<if test=\"roleId != null\"> "
            + "AND sur.role_id = #{roleId} "
            + "</if> "

            + "<if test=\"roleIds != null and roleIds.size != 0 \"> "
            + "AND sur.role_id in "
            + "<foreach collection=\"roleIds\" item=\"id\" index=\"index\" "
            + "                     open=\"(\" close=\")\" separator=\",\"> "
            + "                #{id} "
            + "            </foreach> "
            + "</if> "

            + "<if test=\"userIds != null and userIds.size != 0 \"> "
            + "AND su.id in "
            + "<foreach collection=\"userIds\" item=\"id\" index=\"index\" "
            + "                     open=\"(\" close=\")\" separator=\",\"> "
            + "                #{id} "
            + "            </foreach> "
            + "</if> "

            + "group by su.id "

            + "<if test=\"pageSize != null and offset != null\"> "
            + "limit #{pageSize} offset #{offset} "
            + "</if> "

            + "</script>")
    List<SysUser> selectByCondition(@Param("userOnlineStatus") Integer userOnlineStatus,
                                    @Param("account") String account,
                                    @Param("regionCode") String regionCode,
                                    @Param("areaRecursion") boolean areaRecursion,
                                    @Param("roleId") Long roleId,
                                    @Param("roleIds") List<Long> roleIds,
                                    @Param("userIds") List<Long> userIds,
                                    @Param("pageSize") Integer pageSize,
                                    @Param("offset") Integer offset);

    @Select("<script> "
            + "select count(distinct su.id) "
            + "from sys_user su "
            + "inner join sys_user_role sur on su.id = sur.user_id "
            + "where su.is_deleted = 1 "

            + "<if test=\"account != null\"> "
            + "and concat_ws('', su.user_name, su.real_name) like concat('%', #{account}, '%') "
            + "</if> "

            + "<if test=\"regionCode != null and regionCode != '' and areaRecursion == true \"> "
            + "and su.district_code like concat(#{regionCode},'%') "
            + "</if> "

            + "<if test=\"regionCode != null and regionCode != '' and areaRecursion == false \"> "
            + "and su.district_code = #{regionCode} "
            + "</if> "

            + "<if test=\"userOnlineStatus != null\"> "
            + "AND su.user_online_status = #{userOnlineStatus} "
            + "</if> "

            + "<if test=\"roleId != null\"> "
            + "AND sur.role_id = #{roleId} "
            + "</if> "

            + "<if test=\"roleIds != null and roleIds.size != 0 \"> "
            + "AND sur.role_id in "
            + "<foreach collection=\"roleIds\" item=\"id\" index=\"index\" "
            + "                     open=\"(\" close=\")\" separator=\",\"> "
            + "                #{id} "
            + "            </foreach> "
            + "</if> "

            + "<if test=\"userIds != null and userIds.size != 0 \"> "
            + "AND su.id in "
            + "<foreach collection=\"userIds\" item=\"id\" index=\"index\" "
            + "                     open=\"(\" close=\")\" separator=\",\"> "
            + "                #{id} "
            + "            </foreach> "
            + "</if> "

            + "</script>")
    long count(@Param("userOnlineStatus") Integer userOnlineStatus,
               @Param("account") String account,
               @Param("regionCode") String regionCode,
               @Param("areaRecursion") boolean areaRecursion,
               @Param("roleId") Long roleId,
               @Param("roleIds") List<Long> roleIds,
               @Param("userIds") List<Long> userIds);

    @Select("<script> "
            + "select su.id, su.longitude, su.latitude, su.user_online_status, su.district_code, su.mobile  "
            + "from sys_user su "
            + "where su.is_deleted = 1 "

            + "<if test=\"regionCode != null and regionCode != ''\"> "
            + "and su.district_code like concat(#{regionCode},'%') "
            + "</if> "

            + "AND st_distance(st_geometry('point ('||longitude||' '||latitude||')'), "
            + "st_geometry('point(${longitude} ${latitude})'))*111.1949 <![CDATA[<= ]]> #{distance}::numeric"

            + "</script>")
    List<SysUser> selectByDistance(@Param("longitude") String longitude,
                                   @Param("latitude") String latitude,
                                   @Param("regionCode") String regionCode,
                                   @Param("distance") String distance);

    @Select("<script> "
            + "select su.id, su.longitude, su.latitude, su.user_online_status, su.district_code, su.mobile  "
            + "from sys_user su "
            + "where su.is_deleted = 1 "
            + "and su.id = #{id} "
            + "</script>")
    SysUser selectByUserId(@Param("id") Long id);

    @Select("<script> "
            + "select su.id, su.longitude, su.latitude, su.user_online_status, su.district_code, su.mobile  "
            + "from sys_user su "
            + "where su.is_deleted = 1 "

            + "<if test=\"mobiles != null and mobiles.size != 0 \"> "
            + "AND su.mobile in "
            + "<foreach collection=\"mobiles\" item=\"id\" index=\"index\" "
            + "                     open=\"(\" close=\")\" separator=\",\"> "
            + "                #{id} "
            + "            </foreach> "
            + "</if> "

            + "</script>")
    List<SysUser> selectByMobile(List<String> mobiles);

    @Select("<script> "
            + "select su.id, su.longitude, su.latitude, su.user_online_status, su.district_code, su.mobile  "
            + "from sys_user su "
            + "where su.is_deleted = 1 "

            + "<if test=\"realName != null and realName != ''\"> "
            + "and su.real_name = #{realName} "
            + "</if> "

            + "</script>")
    SysUser selectByRealName(@Param("realName") String realName);

    @Select("<script> "
            + "select su.id, su.longitude, su.latitude, su.user_online_status, su.district_code, su.mobile  "
            + "from sys_user su "
            + "where su.is_deleted = 1 "

            + "<if test=\"ids != null and ids.size != 0 \"> "
            + "AND su.id in "
            + "<foreach collection=\"ids\" item=\"id\" index=\"index\" "
            + "                     open=\"(\" close=\")\" separator=\",\"> "
            + "                #{id} "
            + "            </foreach> "
            + "</if> "

            + "</script>")
    List<SysUser> selectByIds(List<Long> ids);

}
